/*
 * Copyright (C) 2014 The Android Open Source Project
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.folderit.apps.rooms.data;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.folderit.apps.rooms.data.RoomsContract.ReservationEntry;
import com.folderit.apps.rooms.data.RoomsContract.RoomEntry;
import com.folderit.apps.rooms.data.RoomsContract.UserEntry;

/**
 * Manages a local database.
 */
public class RoomsDbHelper extends SQLiteOpenHelper {

    // If you change the database schema, you must increment the database version.
    private static final int DATABASE_VERSION = 1;

    public static final String DATABASE_NAME = "rooms.db";
    private static final String LOG_TAG = RoomsDbHelper.class.getName();

    public RoomsDbHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        // Create a table to hold users.
        final String SQL_CREATE_USER_TABLE = "CREATE TABLE " + UserEntry.TABLE_NAME + " (" +
                UserEntry._ID + " INTEGER PRIMARY KEY," +
                UserEntry.COLUMN_NAME + " TEXT NOT NULL, " +
                UserEntry.COLUMN_PHONE_NUMBER + " TEXT UNIQUE NOT NULL, " +
                UserEntry.COLUMN_EMAIL + " TEXT UNIQUE NOT NULL, " +
                "UNIQUE (" + UserEntry.COLUMN_PHONE_NUMBER  +") ON CONFLICT REPLACE "+
                "UNIQUE (" + UserEntry.COLUMN_EMAIL  +") ON CONFLICT REPLACE"+
                " );";

        // Create a table to hold rooms.
        final String SQL_CREATE_ROOM_TABLE = "CREATE TABLE " + RoomEntry.TABLE_NAME + " (" +
                RoomEntry._ID + " INTEGER PRIMARY KEY," +
                RoomEntry.COLUMN_ROOM_NAME + " TEXT UNIQUE NOT NULL, " +
                RoomEntry.COLUMN_DESCRIPTION + " TEXT NULL, " +
                RoomEntry.COLUMN_COLOR_ID + " INTEGER NOT NULL, " +
                RoomEntry.COLUMN_EXTERNAL_ID + " INTEGER UNIQUE NOT NULL, " +
                "UNIQUE (" + RoomEntry.COLUMN_ROOM_NAME +") ON CONFLICT REPLACE "+
                "UNIQUE (" + RoomEntry.COLUMN_EXTERNAL_ID +") ON CONFLICT IGNORE"+
                " );";

        final String SQL_CREATE_RESERVATION_TABLE = "CREATE TABLE " + ReservationEntry.TABLE_NAME + " (" +
                ReservationEntry._ID + " INTEGER PRIMARY KEY," +
                ReservationEntry.COLUMN_ROOM_KEY + " INTEGER NOT NULL, " + // FK to room table
                ReservationEntry.COLUMN_USER_KEY + " INTEGER NOT NULL, " + // FK to user table
                ReservationEntry.COLUMN_BEGIN + " TEXT NOT NULL, " +
                ReservationEntry.COLUMN_END + " TEXT NOT NULL, " +
                ReservationEntry.COLUMN_CREATED + " TEXT NOT NULL, " +
                ReservationEntry.COLUMN_EVENT_TITLE + " TEXT, " +
                ReservationEntry.COLUMN_EXTERNAL_ID +  " INTEGER UNIQUE NOT NULL, " +

                "UNIQUE (" + ReservationEntry.COLUMN_EXTERNAL_ID +") ON CONFLICT REPLACE, " +

                // Set up the room_id column as a foreign key to room table.
                " FOREIGN KEY (" + ReservationEntry.COLUMN_ROOM_KEY + ") REFERENCES " +
                RoomEntry.TABLE_NAME + " (" + RoomEntry._ID + "), " +

                // Set up the user_id column as a foreign key to user table.
                " FOREIGN KEY (" + ReservationEntry.COLUMN_USER_KEY + ") REFERENCES " +
                UserEntry.TABLE_NAME + " (" + UserEntry._ID + ")" +
                " );" ;

        Log.v(LOG_TAG, "User table script: " + SQL_CREATE_USER_TABLE);
        Log.v(LOG_TAG, "Room table script: " + SQL_CREATE_ROOM_TABLE);
        Log.v(LOG_TAG, "Reservation table script: " + SQL_CREATE_RESERVATION_TABLE);

        sqLiteDatabase.execSQL(SQL_CREATE_USER_TABLE);
        sqLiteDatabase.execSQL(SQL_CREATE_ROOM_TABLE);
        sqLiteDatabase.execSQL(SQL_CREATE_RESERVATION_TABLE);
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        // This database is only a cache for online data, so its upgrade policy is
        // to simply to discard the data and start over
        // Note that this only fires if you change the version number for your database.
        // It does NOT depend on the version number for your application.
        // If you want to update the schema without wiping data, commenting out the next 2 lines
        // should be your top priority before modifying this method.
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + UserEntry.TABLE_NAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + RoomEntry.TABLE_NAME);
        sqLiteDatabase.execSQL("DROP TABLE IF EXISTS " + ReservationEntry.TABLE_NAME);
        onCreate(sqLiteDatabase);
    }
}
